package com.tianlan.blog.dao;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import com.tianlan.blog.dto.QueryEmployeeAttendance;
import com.tianlan.blog.entity.EmployeeAttendance;
import com.tianlan.common.page.MyPage;

public interface EmployeeAttendanceDao extends JpaRepository<EmployeeAttendance, Long>{
	
	@Query(value ="SELECT "
			+ "id,"
			+ "job_number,"
			+ "employee_name,"
			+ "dept,"
			+ "standard_work_hour,"
			+ "actual_work_hour,"
			+ "late_number,"
			+ "late_minute,"
			+ "leave_early_number,"
			+ "leave_early_minute,"
			+ "overtime_hour,"
			+ "holiday_overtime_hour,"
			+ "attendance_day,"
			+ "business_trip_day,"
			+ "absenteeism_day,"
			+ "ask_for_leave_day,"
			+ "standard_additional_wage,"
			+ "overtime_additional_wage,"
			+ "allowance_additional_wage,"
			+ "late_deduction_wage,"
			+ "ask_for_leave_deduction_wage,"
			+ "deduct_deduction_wage,"
			+ "real_wage,"
			+ "remarks,"
			+ "img_url,"
			+ "creator,"
			+ "create_time,"
			+ "operator,"
			+ "update_time,"
			+ "del_flag\n"
			+ "FROM employee_attendance\n"
			+ "WHERE del_flag = '0'\n"
			+ "AND (id = :#{#employee.id} OR :#{#employee.id} is null)\n"
			+ "AND (job_number like concat('%', :#{#employee.jobNumber}, '%') OR :#{#employee.jobNumber} is null)\n"
			+ "AND (employee_name like concat('%', :#{#employee.employeeName}, '%') OR :#{#employee.employeeName} is null OR :#{#employee.employeeName} = '')\n"
			+ "AND (dept like concat('%', :#{#employee.dept}, '%') OR :#{#employee.dept} is null OR :#{#employee.dept} = '')\n"
			+ "AND (DATE_FORMAT(create_time,'%Y-%m-%d') >= DATE_FORMAT(:#{#employee.startDate},'%Y-%m-%d') or :#{#employee.startDate} is null)\n"
			+ "AND (DATE_FORMAT(create_time,'%Y-%m-%d') <= DATE_FORMAT(:#{#employee.endDate},'%Y-%m-%d') or :#{#employee.endDate} is null)\n"
			+ "ORDER BY create_time DESC LIMIT :#{#page.start},:#{#page.size}"
	,nativeQuery = true)
	List<EmployeeAttendance> selectEmployeeAttendanceList(@Param("employee") QueryEmployeeAttendance employee,@Param("page") MyPage<EmployeeAttendance> page);
	
	List<EmployeeAttendance> findAllByDelFlag(String delFlag);
	
	@Query(value ="SELECT COUNT(1)"
			+ "FROM employee_attendance\n"
			+ "WHERE del_flag = '0'\n"
			+ "AND (id = :#{#employee.id} OR :#{#employee.id} is null)\n"
			+ "AND (job_number like concat('%', :#{#employee.jobNumber}, '%') OR :#{#employee.jobNumber} is null)\n"
			+ "AND (employee_name like concat('%', :#{#employee.employeeName}, '%') OR :#{#employee.employeeName} is null OR :#{#employee.employeeName} = '')\n"
			+ "AND (dept like concat('%', :#{#employee.dept}, '%') OR :#{#employee.dept} is null OR :#{#employee.dept} = '')\n"
			+ "AND (DATE_FORMAT(create_time,'%Y-%m-%d') >= DATE_FORMAT(:#{#employee.startDate},'%Y-%m-%d') or :#{#employee.startDate} is null)\n"
			+ "AND (DATE_FORMAT(create_time,'%Y-%m-%d') <= DATE_FORMAT(:#{#employee.endDate},'%Y-%m-%d') or :#{#employee.endDate} is null)\n"
			+ "ORDER BY create_time DESC"
	,nativeQuery = true)
	int selectEmployeeAttendanceCount(@Param("employee") QueryEmployeeAttendance employee);
	
	@Transactional
	@Modifying
	@Query(value = "UPDATE employee_attendance SET img_url = ?1 WHERE id = ?2",nativeQuery = true)
	int updateImg(String imgUrl,Integer id);
}

